﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using LumenWorks.Framework.IO.Csv;


namespace LP_Csharp
{
    public partial class estatisticas : UserControl
    {
        //Declacaracao da tabela para as estatisticas
        DataTable table = new DataTable();


        public estatisticas(Control parent)
        {
            InitializeComponent();
            this.Parent = parent;
            this.Dock = DockStyle.Fill;
            this.Show();
        }

        /*##############################################################################################################*/
        private void button1_Click(object sender, EventArgs e)
        {
            // Criacao da Tabela para as estatisticas
            SQLiteConnection conect = new SQLiteConnection("Data Source = DataBase.db");
            conect.Open();
            SQLiteCommand command = conect.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT * FROM DOCENTES";
            SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command);
            dataAdapter.Fill(this.table);
            conect.Close();


            //Cria uma query selecionando os nomes dos estabecimentos
            var queryComBox2 = from col in table.AsEnumerable()
                               orderby col.Field<string>("nome_estab")
                               select col.Field<string>("nome_estab");

            //Cria uma query selecionando os anos lectivos
            var queryComBox3 = from col in table.AsEnumerable()
                               orderby col.Field<Int64>("ano_lectivo")
                               select col.Field<Int64>("ano_lectivo");

            //lista as querys nas comboboxs
            this.comboBox2.DataSource = queryComBox2.Distinct().ToList();
            this.comboBox3.DataSource = queryComBox3.Distinct().ToList();

            //informacao para que o user saiba que ja foi criada a tabela para consulta
            MessageBox.Show("A tabela foi criada!", "Informação");
        }

        /*##############################################################################################################*/
        private void radioButton1_CheckedChanged(object sender, EventArgs e)
        {
            //condicao caso seja seleciondada a categoria
            if (this.radioButton1.Checked)
            {
                this.label1.Text = "Categorias";
                
                //Cria uma query selecionando a categoria
                var queryComBox1 = from col in table.AsEnumerable()
                                   orderby col.Field<string>("categoria")
                                   select col.Field<string>("categoria");

                //lista a query na combobox
                this.comboBox1.DataSource = queryComBox1.Distinct().ToList();
            }
        }

        /*##############################################################################################################*/
        private void radioButton2_CheckedChanged(object sender, EventArgs e)
        {
            //condicao caso seja seleciondado o Grau academico
            if (this.radioButton2.Checked)
            {
                this.label1.Text = "Grau Academico";
                
                //Cria uma query selecionando o grau
                var queryComBox1 = from col in table.AsEnumerable()
                                   orderby col.Field<string>("ultimo_grau")
                                   select col.Field<string>("ultimo_grau");

                //lista a query na combobox
                this.comboBox1.DataSource = queryComBox1.Distinct().ToList();
            }
        }

        /*##############################################################################################################*/
        //Metodo para calcular o valor da estatistica
        private void valorEstatistic()
        {
            //caso esteja selecionado as categorias
            if (this.radioButton1.Checked)
            {
                //declaracao de variaveis com valores escolhidos nas bombobox
                string cB1 = (string)comboBox1.SelectedItem;
                string cB2 = (string)comboBox2.SelectedItem;
                Int64 cB3 = (Int64)comboBox3.SelectedItem;

                //query que conta o total de professores consoante as bomboboxs
                int total = (from val in table.AsEnumerable()
                             where val.Field<string>("categoria") == cB1 &&
                             val.Field<string>("nome_estab") == cB2 &&
                             val.Field<Int64>("ano_lectivo") == cB3
                             select val).Count();

                //mostra o valor da query
                this.label5.Text = total + "";
            }

            //caso esteja selecionado as o grau
            if (this.radioButton2.Checked)
            {
                //declaracao de variaveis com valores escolhidos nas bombobox
                string cB1 = (string)comboBox1.SelectedItem;
                string cB2 = (string)comboBox2.SelectedItem;
                Int64 cB3 = (Int64)comboBox3.SelectedItem;

                //query que conta o total de professores consoante as bomboboxs
                int total = (from val in table.AsEnumerable()
                             where val.Field<string>("ultimo_grau") == cB1 &&
                             val.Field<string>("nome_estab") == cB2 &&
                             val.Field<Int64>("ano_lectivo") == cB3
                             select val).Count();

                //mostra o valor da query
                this.label5.Text = total + "";
            }

        }

        /*##############################################################################################################*/
        // METODO DOS COMBOBOX
        // sempre que o user clica num valor da Combobox e executado o valorEstatistc()
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
           valorEstatistic();
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
           valorEstatistic();
        }

        private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
        {
            valorEstatistic();
        }

        private void estatisticas_Load(object sender, EventArgs e)
        {

        }


    }
}
